<?php
/**
 * db_mysql mysql操作基类
 * @author yangzhiguo www.amiaoagou.com <yangzhiguo0903@163.com>
 */
defined('IN_LK') or die('Access Denied');
final class db_mysql
{
    /**
     * 数据库连接id
     */
	public $connid;
	
    /**
     * sql执行次数
	 */
	public $querynum = 0;
	
    /**
     * 当前数据库名称
	 */
	public $dbname;
	
	public function __construct()
	{
	}
	
	public function connect($db_host = DB_HOST, $db_user = DB_USER, $db_pass = DB_PASS, $db_name = DB_NAME, $db_pcon = 0, $db_char = '')
	{
		$func = $db_pcon == 1 ? 'mysql_pconnect' : 'mysql_connect';
		if(!$this->connid = @$func($db_host, $db_user, $db_pass))
		{
			$this->halt($this->error());
		}
		if($this->version()> '4.1')
		{
			$char = $db_char ? 
			"character_set_connection='$db_char',character_set_results='$db_char',character_set_client=binary" : '';
			$char .= $this->version()> '5.0.1' ? (empty($char) ? '' : ',') . 'sql_mode=""' : '';
			$char && mysql_query("SET {$char}", $this->connid);
		}
		
		if($db_name && !@mysql_select_db($db_name, $this->connid))
		{
			$this->halt(mysql_error());
		}
		$this->dbname = $db_name;
		return $this->connid;
	}
	
    /**
     * 执行sql
	 * @param string $sql 需要执行的sql
	 * @param string $type UNBUFFERED | SLIENT 不获取结果 | 静默执行
	 */
	public function query($sql, $type = '')
	{
		if(!is_resource($this->connid))
		{
			$this->connect();
		}
		$sql = trim($sql);
		$func = $type == 'UNBUFFERED' ? 'mysql_unbuffered_query' : 'mysql_query';
		if(!($res = @$func($sql, $this->connid)) && $type != 'SLIENT')
		{
			$this->halt('MYSQL QUERY ERROR', $sql);
			return false;
		}
		$this->querynum++;
		return $res;
	}
	
    /**
     * 查询
	 * @param string $field 字段    'id,name'
	 * @param string $table 数据表  'table'
	 * @param string $where 条件    '`id`=1 AND ...'	需要加`
	 * @param string $order 排序
	 * @param string $group 分组
	 * @param string $limit 查询条数
	 * @param string $key   关键字段
	 * @return array        查询结果集的数组
	 */
	public function get($field, $table, $where = '', $order = '', $group = '', $limit = '', $key = '')
	{
		$where  = $where ? "WHERE $where" : '';
		$order  = $order ? "ORDER BY $order" : '';
		$group  = $group ? "GROUP BY $group" : '';
		$limit  = $limit ? "LIMIT $limit" : '';
		if($field && $field != '*')
		{
			$field_arr = explode(',', $field);
			$field_arr = array_map(array($this, 'add_wave'), $field_arr);
			$field = implode(',', $field_arr);
		}
		else
		{
			$field = '*';
		}
		$sql = "SELECT $field FROM `" . $this->dbname . "`.`$table` $where $group $order $limit";
		$res = $this->query($sql);
		if(!is_resource($res))
		{
			return $res;
		}
		$data = array();
		while($row = $this->fetch_array($res))
		{
			if($key && isset($row[$key]))
			{
				$data[$row[$key]] = $row;
			}
			else
			{
				$data[] = $row;
			}
		}
		$this->free_result($res);
		return $data;
	}
	
    /**
     * 查询单条记录
	 * @param string $field 字段    'id,name'
	 * @param string $table 数据表  'table'
	 * @param string $where 条件    '`id`=1 AND ...'	需要加`
	 * @param string $order 排序
	 * @param string $group 分组
	 * @return array        查询结果集的数组
	 */
	public function get_one($field, $table, $where = '', $order = '', $group = '')
	{
		$where  = $where ? "WHERE $where" : '';
		$order  = $order ? "ORDER BY $order" : '';
		$group  = $group ? "GROUP BY $group" : '';
		$limit  = 'LIMIT 1';
		if($field && $field != '*')
		{
			$field_arr = explode(',', $field);
			$field_arr = array_map(array($this, 'add_wave'), $field_arr);
			$field = implode(',', $field_arr);
		}
		else
		{
			$field = '*';
		}
		$sql   = "SELECT $field FROM `" . $this->dbname . "`.`$table` $where $group $order $limit";
		$res   = $this->query($sql);
		if(!is_resource($res))
		{
			return $res;
		}
		$data = array();
		$data = $this->fetch_array($res);
		$this->free_result($res);
		return $data;
	}
	
    /**
     * 插入数据表数据
	 * @param array    $data      键值对数组
	 * @param string   $tablename 表名
	 * @param boolean  $rid       是否返回插入的id 
	 * @param boolean  $replace   是否替换
	 * @return boolan / int       是否成功或插入的id
	 */
	public function insert($data, $tablename, $rid = true, $replace = false)
	{
		if(!is_array($data) || empty($data) || !$tablename)
		{
			return false;
		}
		$field_arr = array_keys($data);
		$value_arr = array_values($data);
		$field_arr = array_map(array($this, 'add_wave'), $field_arr);
		$value_arr = array_map(array($this, 'add_quote'), $value_arr);
		$field = implode(',', $field_arr);
		$value = implode(',', $value_arr);
		$ins = $replace ? 'REPLACE' : 'INSERT';
		$sql = "$ins INTO `$this->dbname`.`$tablename` ($field) VALUES($value)";
		$res = $this->query($sql);
		return $rid && $this->insert_id() ? $this->insert_id() : $res;
	}

    /**
     * 更新数据
	 * @param array    $data      键值对数组 array('name'=>'abc','logintimes'=>'+=1','lost'=>'-=1')
	 * @param string   $tablename 表名
	 * @param string   $where     条件
	 * @param boolean  $raffect   是否返回影响行数
	 * @param boolean/int         返回影响的行数或是执行成功
	 */
	public function update($data, $tablename, $where, $raffect = true)
	{
		$fields = array();
		if(!is_array($data) || empty($data) || !$tablename || !$where)
		{
			return false;
		}
		foreach($data as $_field => $_value)
		{
			switch(substr($_value, 0, 2))
			{
				case '+=':
				case '-=':
					$num = substr($_value, 2);
					if(!is_numeric($num))
					{
						continue;
					}
					else
					{
						$key = substr($_value, 0, 1);
						$fields[] = $this->add_wave($_field) . '=' . $this->add_wave($_field) . $key . $this->add_quote($num);
					}
					break;
				default:
					$fields[] = $this->add_wave($_field) . '=' . $this->add_quote($_value);
					break;
			}
		}
		$where  = "WHERE $where";
		$key_value = implode(',', $fields);
		$sql = "UPDATE `$this->dbname`.`$tablename` SET $key_value $where";
		$res = $this->query($sql);
		if($raffect)
		{
			return $this->affected_rows();
		}
		return $res;
	}
	
    /**
     * 删除数据
	 * @param string  $tablename 表名
	 * @param strign  $where     条件
	 * @param boolean $raffect   返回影响的行数
	 * @param boolean 返回是否成功
	 */
	public function delete($tablename, $where, $raffect = true)
	{
		if(!$tablename || !$where)
		{
			return false;
		}
		$where = "WHERE $where";
		$sql = "DELETE FROM `$this->dbname`.`$tablename` $where";
		$res = $this->query($sql);
		return $raffect ? $this->affected_rows() : $res;
	}
	
    /**
     * 获得数据表主键
	 * @param string $tablename 表名
	 * @return string 主键字段名
	 */
	public function get_primary($tablename)
	{
		$res = $this->query("SHOW COLUMNS FROM `$this->dbname`.`$tablename`");
		while($row = $this->fetch_array($res))
		{
			if($row['Key'] == 'PRI')
			break;
		}
		$this->free_result($res);
		return $row['Field'];
	}
	
    /**
     * 检查表是否存在
	 * @param string $tablename 表名
	 * @return boolean 存在[true] 不存在[false]
	 */
	public function check_table_exists($tablename)
	{
		return in_array($tablename, $this->list_tables());
	}
	
    /**
     * 取得所有表名称
	 * @return array 索引数组
	 */
	public function list_tables()
	{
		$tables = array();
		$res    = $this->query('SHOW TABLES');
		while($row = $this->fetch_array($res, MYSQL_NUM))
		{
			$tables[] = $row[0];
		}
		$this->free_result($res);
		return $tables;
	}
	
    /**
     * 检查字段是否存在于某表中
     * @param string $fieldname 字段名
	 * @param string $tablename 表名
	 * @return boolean 存在[true] 不存在[false]
	 */
	public function check_field_exists($fieldname, $tablename)
	{
		return array_key_exists($fieldname, $this->list_fields($tablename));
	}
	
    /**
     * 取表字段
	 * @param string $tablename 表名
	 * @return array 索引数组
	 */
	public function list_fields($tablename)
	{
		if(!$this->check_table_exists($tablename))
		{
			return false;
		}
		$fields = array();
		$res = $this->query("SHOW COLUMNS FROM `$this->dbname`.`$tablename`");
		while($row = $this->fetch_array($res))
		{
			$fields[$row['Field']] = $row['Type'];
		}
		$this->free_result($res);
		return $fields;
	} 
	
    /**
     * 返回结果集中行的数目。此命令仅对 SELECT 语句有效
	 * @param resource $res 结果集
	 * @return int
	 */
	public function num_rows($res)
	{
		return mysql_num_rows($res);
	}
	
    /**
	 * 取得结果集中字段的数目
	 * @param resource $res 结果集
	 * @return int
	 */
	public function num_fields($res)
	{
		return mysql_num_fields($res);
	}
		
    /**
     * 返回影响的行数
	 */
	private function affected_rows()
	{
		return mysql_affected_rows($this->connid);
	}
	
    /**
     * 取得上一步 INSERT 操作产生的ID
	 */
	private function insert_id()
	{
		return mysql_insert_id($this->connid);
	}
	
    /**
     * 释放结果集
	 */
	private function free_result(&$res)
	{
		return mysql_free_result($res);
	}
	
    /**
     * 从结果集中取出一条信息
	 * @param resource $res 结果集资源
	 * @param int      $type 默认关联数组
	 * @return array   结果集转成的数组
	 */
	private function fetch_array($res, $type = MYSQL_ASSOC)
	{
		switch($type)
		{
			case MYSQL_ASSOC: //关联数组
				return mysql_fetch_assoc($res);
				break;
			case MYSQL_NUM:	//枚举数组
				return mysql_fetch_row($res);
				break;
			case MYSQL_BOTH:
				return mysql_fetch_array($res, MYSQL_BOTH);
				break;
		}
	}
	
    /**
     * 返回数据库版本
	 */
	private function version()
	{
		static $version = '';
		if(!$version)
		{
			preg_match('/\d\.\d+\.\d+/i', mysql_get_server_info($this->connid), $d);
			$version = $d[0];
		}
		return $version;
	}
	
    /**
     * 错误信息
	 */
	private function error()
	{
		return $this->connid ? mysql_error($this->connid) : mysql_error();
	}
	
    /**
     * 错误号
	 */
	private function errno()
	{
		return $this->connid ? (int)mysql_errno($this->connid) : (int)mysql_errno();
	}
	
    /**
     * 错误及调试信息
	 */
	private function halt($message = '', $sql = '')
	{
		$style = "<style>div{font-size:12px;border:1px solid #FF3300; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif; background:#FFFFCC;}ul{list-style:none;padding:0px;margin:0px;}li{color:red;}li span{color:green;}</style>";
		$errmsg  = "<ul><li><span>Sql Query:</span>$sql</li>";
		$errmsg .= '<li><span>Error Type:</span>' . $this->error() . '</li>';
		$errmsg .= '<li><span>Error No.:</span>' . $this->errno() . '</li>';
		$errmsg .= "<li><span> Message:</span>$message</li>";
		$errmsg .= "<li><span> Location:</span>" . __FILE__ . ' ' . __LINE__ . " line</li>";
		$msg = (defined('ADMIN') || DEBUG) ? $errmsg : 'Bad Request';
		echo "<div>$msg</div>", $style;
		exit;
	}
	
    /**
     * 按,分割字符串并加上`
	 * @param string $string 需要加`的字符串
	 */
	private function add_wave($string)
	{
		if($string != '*' && strpos($string, '`') === false && strpos($string, '(') === false && strpos($string, '.') === false)
		{
			return '`' . trim($string) . '`';
		}
		else
		{
			return $string;
		}
	}
	
    /**
     * 字符串及数字加引号
	 * @param string $string 需要加'的字符串
	 */
	private function add_quote($string)
	{
		return "'" . $string . "'";
	}
	
    /**
     * 断开连接
	 */
	public function close()
	{
		return mysql_close($this->connid);
	}
}